from openpyxl import Workbook
import requests
import json
from datetime import date, datetime


wb = Workbook()
sheet = wb.active

def get_provinces():
	url = "https://api.oneweb.mercedes-benz.com.cn/ow-dealers-location/provinces/query?needFilterByDealer=true&needFilterByModel=false&modelName=null";
	requests.packages.urllib3.disable_warnings()
	res = requests.get(url, verify=False)
	provinces = json.loads(res.text)["result"]
	return provinces

def get_cities_by_province_id(province_id):
	url = f"https://api.oneweb.mercedes-benz.com.cn/ow-dealers-location/cities/query?needFilterByDealer=true&provinceId={province_id}&needFilterByModel=false&modelName="
	res = requests.get(url, verify=False)
	cities = json.loads(res.text)["result"]
	return cities

def get_dealer_by_city_name(city_name):
	url = f"https://api.oneweb.mercedes-benz.com.cn/ow-dealers-location/dealers/query?sort=alphabetical&city={city_name}&longitude=&latitude=&keywords=&dealerId=&needFilterByModel=false&modelName="
	res = requests.get(url, verify=False)
	dealers = json.loads(res.text)["result"]
	return dealers

def get_items():
	items = []
	provinces = get_provinces()
	for province in provinces:
		province_id = province["id"]
		province_name = province["name"]
		cities = get_cities_by_province_id(province_id)
		for city in cities:
			city_name = city["name"]
			dealers = get_dealer_by_city_name(city_name)
			for dealer in dealers:
				remarks = "/"
				services = dealer["service_scope"]
				if services != None and len(services) != 0:
					for service in services:
						t = t1 = t2 = []
						if service["categoryCode"] == "sales":
							types = service["types"]
							t1 = [type["name"] for type in types]
						if service["categoryCode"] == "services":
							types = service["types"]
							t2 = [type["name"] for type in types]
						t.extend(t1)
						t.extend(t2)
						remarks = remarks.join(t)
						print(remarks)
				item = {
					"dealer_name" : dealer["displayName"],
					"brand_id" : None,
					"address" : dealer["address"],
					"brand" : "奔驰",
					"province" : dealer["province"],
					"city" : dealer["city"],
					"sale_call" : dealer["phoneNumber"],
					"customer_service_call" : dealer["afterSaleServicePhoneNumber"],
					"update_time" : datetime.now(),
					"longitude" : dealer["longitude"],
					"latitude" : dealer["latitude"],
					"dealer_type" : None,
					"manufacturer_id" : None,
					"manufacturer" : "梅赛德斯-奔驰",
					"state" : None,
					"opening_date" : None,
					"close_date" : None,
					"dealer_id_web" : None,
					"controlling_shareholder" : None,
					"other_shareholders" : None,
					"status" : None,
					"remarks" : remarks,
				}
				items.append(item)
	return items			

def main():
	items = get_items()
	# 设置表头
	sheet["A1"] = "经销商名称"
	sheet["B1"] = "电话"
	sheet["C1"] = "服务电话"
	sheet["D1"] = "地址"
	sheet["E1"] = "乘用车"
	sheet["F1"] = "商务车"
	sheet["G1"] = "AMG性能中心"
	sheet["H1"] = "大客户中心"
	sheet["I1"] = "服务中心"
	sheet["J1"] = "She's Mercedes"
	sheet["K1"] = "星睿认证二手车"
	sheet["L1"] = "梅赛德斯-奔驰金融服务"
	sheet["M1"] = "省份"
	sheet["N1"] = "城市"
	sheet["R1"] = "梦想座驾"
	sheet["P1"] = "迈巴赫"
	sheet["Q1"] = "G级车"



	for line in range(len(items)):
		sheet['A'+str(line+2)] = items[line]["dealer_name"]
		sheet['B'+str(line+2)] = items[line]["sale_call"]
		sheet['C'+str(line+2)] = items[line]["customer_service_call"]
		sheet['D'+str(line+2)] = items[line]["address"]
		sheet['E'+str(line+2)] = "√" if "乘用车" in items[line]["remarks"] else ""
		sheet['F'+str(line+2)] = "√" if "商务车" in items[line]["remarks"] else ""
		sheet['G'+str(line+2)] = "√" if "AMG" in items[line]["remarks"] else ""
		sheet['H'+str(line+2)] = "√" if "大客户中心" in items[line]["remarks"] else ""
		sheet['I'+str(line+2)] = "√" if "服务中心" in items[line]["remarks"] else ""
		sheet['J'+str(line+2)] = "√" if "She's Mercedes" in items[line]["remarks"] else ""
		sheet['K'+str(line+2)] = "√" if "星睿二手车" in items[line]["remarks"] else ""
		sheet['L'+str(line+2)] = "√" if "奔驰金融服务" in items[line]["remarks"] else ""
		sheet['R'+str(line+2)] = "√" if "梦想座驾" in items[line]["remarks"] else ""
		sheet['P'+str(line+2)] = "√" if "迈巴赫" in items[line]["remarks"] else ""
		sheet['Q'+str(line+2)] = "√" if "G级车" in items[line]["remarks"] else ""

		sheet['M'+str(line+2)] = items[line]["province"]
		sheet['N'+str(line+2)] = items[line]["city"]



	wb.save(r'./奔驰'+str(date.today())+'.xlsx')
if __name__ == '__main__':
	main()
